package util;

import java.sql.Connection;

//正则表达式
import java.util.regex.*;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import java.sql.ResultSet;

import java.util.LinkedList;
import java.io.*;

public class RewriteDataBase {
	public static float rounder1(float x) {
		return (float) (((int) Math.round(x * 1000)) / 1000.0);
	}

	public static float rounder2(float x) {
		return (float) (((int) Math.round(x * 10000)) / 10000.0);
	}

	public static void rewriteDataBase() throws IOException, SQLException {

		// 连接
		Connection conn = null;
		Statement stmt = null;
		Statement find = null;
		Statement find2 = null;
		ResultSet rs = null;
		conn = DriverManager
				.getConnection("jdbc:mysql://localhost/ani_forespring?"
						+ "user=root&password=");
		find = conn.createStatement();
		find2 = conn.createStatement();
		stmt = conn.createStatement();

		// 正则表达式
		Pattern pattern = Pattern.compile("\\w+");

		// 读取Species

		FileReader fileReader = new FileReader("db/Species.csv");
		BufferedReader bufferedReader = new BufferedReader(fileReader);
		String str = "";
		int count = 0;// 用来计数

		/*
		 * LinkedList<String> family = new LinkedList(); LinkedList<String>
		 * genus = new LinkedList(); LinkedList<String> species = new
		 * LinkedList();
		 * 
		 * // 加入链表 while ((str = bufferedReader.readLine()) != null) { //
		 * 正则表达式匹配 Matcher matcher = pattern.matcher(str); while
		 * (matcher.find()) { count++; count %= 3; switch (count) { case 1:
		 * species.add(matcher.group()); break; case 2:
		 * genus.add(matcher.group()); break; case 0:
		 * family.add(matcher.group()); break; } } }
		 */
		// 写入数据库

		// ---REWRITE---

		// 写入family

		// // 清空数据
		// stmt.execute("delete from family");
		// // 判断是否有重复数据，不重复，则加入
		Boolean judge = true; // 判断变量
		// for (int i = 0; i < family.size(); i++) {
		// judge = true;
		// if (i == family.size() - 1) {
		// // 最后一个，直接加入
		// stmt.execute("insert into family set family_key='"
		// + family.get(i) + "'");
		// break;
		// }
		// for (int j = i + 1; j < family.size(); j++) {
		// // 和之后每一个比较，如果都不同，则加入
		// if (family.get(i).equals(family.get(j))) {
		// judge = false;
		// }
		// }
		// // 不相同，则加入
		// if (judge.equals(true)) {
		// stmt.execute("insert into family set family_key='"
		// + family.get(i) + "'");
		// }
		// }

		// 写入genus(有bug，未去重)

		// rs = find.executeQuery("select * from family");
		// for (int i = 0; i < genus.size();i++) {
		// // 查id
		// rs.first();
		// while (!rs.isAfterLast()) {
		// // System.out.println(rs.getString("family_key"));
		// if (rs.getString("family_key").equals(family.get(i))) {
		// stmt.execute("insert into genus set genus_key='"
		// + genus.get(i) + "'" + ",family_id="
		// + rs.getInt("id"));
		// }
		// rs.next();
		// }
		// }
		// rs.close();

		// rs = find.executeQuery("select * from family");
		// for (int i = 0; i < genus.size();i++) {
		// // 查id
		// rs.first();
		// while (!rs.isAfterLast()) {
		// // System.out.println(rs.getString("family_key"));
		// if (rs.getString("family_key").equals(family.get(i))) {
		// stmt.execute("insert into genus set genus_key='"
		// + genus.get(i) + "'" + ",family_id="
		// + rs.getInt("id"));
		// }
		// rs.next();
		// }
		// }
		// rs.close();

		// 写入specise
		//
		// rs = find.executeQuery("select * from genus");
		// for (int i = 0; i < species.size(); i++) {
		// // 查id
		// rs.first();
		// while (!rs.isAfterLast()) {
		//
		// if (rs.getString("genus_key").equals(genus.get(i))) {
		// stmt.execute("insert into species set species_key='"
		// + species.get(i) + "'" + ",genus_id="
		// + rs.getInt("id") + ",a=0,b=0,c=0");
		// }
		// rs.next();
		// }
		// }

		// ---REWRITE---

		// 关闭数据流
		bufferedReader.close();
		fileReader.close();

		// 读取Combines
		fileReader = new FileReader("db/Combines.csv");
		bufferedReader = new BufferedReader(fileReader);

		pattern = Pattern.compile("[^,]+(?=,||\n)");

		// 加入链表
		LinkedList<Integer> plot = new LinkedList<Integer>();
		LinkedList<Integer> altitude = new LinkedList<Integer>();
		LinkedList<Float> slopeDegree = new LinkedList<Float>();
		LinkedList<Integer> species = new LinkedList<Integer>();
		LinkedList<Float> diameterAtBreastHeight = new LinkedList<Float>();
		LinkedList<Float> height = new LinkedList<Float>();
		LinkedList<Float> latitude = new LinkedList<Float>();
		LinkedList<Float> longitude = new LinkedList<Float>();

		while ((str = bufferedReader.readLine()) != null) {
			Matcher matcher = pattern.matcher(str);
			count = 0;

			while (matcher.find()) {
				switch (count) {
				case 0:
					plot.add(Integer.parseInt(matcher.group()));
					count++;
					break;
				case 1:
					matcher.group();
					count++;
					break;
				case 2:
					matcher.group();
					count++;
					break;
				case 3:
					altitude.add(Integer.parseInt(matcher.group()));
					count++;
					break;
				case 4:
					slopeDegree.add(Float.parseFloat(matcher.group()));
					count++;
					break;
				case 5:
					species.add(Integer.parseInt(matcher.group()));
					count++;
					break;
				case 6:
					diameterAtBreastHeight
							.add(Float.parseFloat(matcher.group()));
					count++;
					break;
				case 7:
					height.add(Float.parseFloat(matcher.group()));
					count++;
					break;
				case 8:
					latitude.add(Float.parseFloat(matcher.group()));
					count++;
					break;
				case 9:
					longitude.add(Float.parseFloat(matcher.group()));
					count++;
					break;
				}
			}
		}

		// System.out.println(plot.get(0));
		// System.out.println(altitude.get(0));
		// System.out.println(slopeDegree.get(0));
		// System.out.println(species.get(0));
		// System.out.println(diameterAtBreastHeight.get(0));
		// System.out.println(height.get(0));
		// System.out.println(latitude.get(0));
		// System.out.println(longitude.get(0));

		// 写入数据库

		// 写入Topography
		// for (int i = 0; i < slopeDegree.size(); i++) {
		//
		// judge = true;
		// if (i == slopeDegree.size() - 1) {
		// // 最后一个，直接加入
		// stmt.execute("insert into topography set "
		// + "geomorphology_id=1," + "slope_aspect_id=1,"
		// + "slope_position_id=1," + "slope_degree="
		// + slopeDegree.get(i));
		// break;
		// }
		// for (int j = i + 1; j < slopeDegree.size(); j++) {
		// // 和之后每一个比较，如果都不同，则加入
		// if (slopeDegree.get(i).equals(slopeDegree.get(j))) {
		// judge = false;
		// }
		// }
		// // 不相同，则加入
		// if (judge.equals(true)) {
		// stmt.execute("insert into topography set "
		// + "geomorphology_id=1," + "slope_aspect_id=1,"
		// + "slope_position_id=1," + "slope_degree="
		// + slopeDegree.get(i));
		// }
		// }

		// 写入AnchorPoint
		// for (int i = 0; i < altitude.size(); i++) {
		//
		// judge = true;
		// if (i == slopeDegree.size() - 1) {
		// // 最后一个，直接加入
		// stmt.execute("insert into anchor_point set " + "longitude="
		// + longitude.get(i) + ",latitude=" + latitude.get(i)
		// + ",altitude=" + altitude.get(i));
		// break;
		// }
		// for (int j = i + 1; j < slopeDegree.size(); j++) {
		// // 和之后每一个比较，如果都不同，则加入
		// if (slopeDegree.get(i).equals(slopeDegree.get(j))) {
		// judge = false;
		// }
		// }
		// // 不相同，则加入
		// if (judge.equals(true)) {
		// stmt.execute("insert into anchor_point set " + "longitude="
		// + longitude.get(i) + ",latitude=" + latitude.get(i)
		// + ",altitude=" + altitude.get(i));
		// }
		// }
		// 写入Plot

//		ResultSet p1, p2;
//
//		p1 = find.executeQuery("select * from topography");
//		p2 = find2.executeQuery("select * from anchor_point");
//
//		for (int i = 0; i < plot.size(); i++) {
//			judge = true;
//			if (i == plot.size() - 1) {
//				// 最后一个，直接加入
//
//				// 需要找到topography_id,anchor_point_id;
//				p1.first();
//
//				while (!p1.isAfterLast()) {
//					if (slopeDegree.get(i).equals(p1.getFloat("slope_degree"))) {
//						break;
//					}
//					p1.next();
//				}
//
//				p2.first();
//
//				while (!p2.isAfterLast()) {
//					if (RewriteDataBase.rounder2(latitude.get(i)) == (p2
//							.getFloat("latitude"))
//							&& RewriteDataBase.rounder1(longitude.get(i)) == (p2
//									.getFloat("longitude"))) {
//						break;
//					}
//					p2.next();
//				}
//
//				stmt.execute("insert into plot set " + "id = " + plot.get(i)
//						+ ",subcompartment_id = 1," + "shrub_id = 1,"
//						+ "topography_id=" + p1.getString("id")
//						+ ",anchor_point_id=" + p2.getString("id")
//						+ ",investigation_id=1," + "canopy_density=0");
//				break;
//			}
//			for (int j = i + 1; j < plot.size(); j++) {
//				// 和之后每一个比较，如果都不同，则加入
//				if (plot.get(i).equals(plot.get(j))) {
//					judge = false;
//				}
//			}
//			// 不相同，则加入
//			if (judge.equals(true)) {
//				p1.first();
//
//				while (!p1.isAfterLast()) {
//					if (slopeDegree.get(i).equals(p1.getFloat("slope_degree"))) {
//						break;
//					}
//					p1.next();
//				}
//
//				p2.first();
//
//				while (!p2.isAfterLast()) {
//					if (RewriteDataBase.rounder2(latitude.get(i)) == (p2
//							.getFloat("latitude"))
//							&& RewriteDataBase.rounder1(longitude.get(i)) == (p2
//									.getFloat("longitude"))) {
//						break;
//					}
//					p2.next();
//				}
//
//				stmt.execute("insert into plot set " + "id = " + plot.get(i)
//						+ ",subcompartment_id = 1," + "shrub_id = 1,"
//						+ "topography_id=" + p1.getString("id")
//						+ ",anchor_point_id=" + p2.getString("id")
//						+ ",investigation_id=1," + "canopy_density=0");
//			}
//		}
		// 写入Tree
//		for( int i = 0; i < plot.size(); i++ ){
//			stmt.execute("insert into tree set plot_id=" + plot.get(i)
//					+",species_id=" + species.get(i)
//					+",diameter_at_breast_height=" + diameterAtBreastHeight.get(i)
//					+",height=" + height.get(i)
//					+",crown_id=1"
//					+",age=0");
//		}
		// 关闭数据流
		bufferedReader.close();
		fileReader.close();
	}
}
